﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;

using OfficeOpenXml;
using BMS.Models;

namespace BMS.Models.BusinessLogic
{
    public class ReportDocument
    {
        public int GetRowsNoFromFile(String existingFilePath)
        {
            int rows = 0;

            using (var package = new ExcelPackage(new FileInfo(existingFilePath)))
            {
                ExcelWorksheet wrkSheet = package.Workbook.Worksheets["Report"];
                rows = wrkSheet.Dimension.End.Row;
            }

            return rows;
        }

        public List<ReportModel> GetRecordsFromFile(String existingFilePath)
        {
            List<ReportModel> reportRecordsList = new List<ReportModel>();

            using (var package = new ExcelPackage(new FileInfo(existingFilePath)))
            {
                ExcelWorksheet wrkSheet = package.Workbook.Worksheets["Report"];

                for(int i = wrkSheet.Dimension.Start.Row + 1; i<= wrkSheet.Dimension.End.Row; i++)
                {
                    ReportModel model = new ReportModel();
                    model.SONoOrRefNo = (wrkSheet.Cells[i, 1].Value == null) ? "" : (String)wrkSheet.Cells[i, 1].Value.ToString();
                    model.Location = (wrkSheet.Cells[i, 2].Value == null) ? "" : (String)wrkSheet.Cells[i, 2].Value.ToString();
                    model.Region = (wrkSheet.Cells[i, 3].Value == null) ? "" : (String)wrkSheet.Cells[i, 3].Value.ToString();
                    model.WONo = (wrkSheet.Cells[i, 4].Value == null)? "" : (String)wrkSheet.Cells[i, 4].Value.ToString();
                    model.PONo = (wrkSheet.Cells[i, 5].Value == null) ? "" : (String)wrkSheet.Cells[i, 5].Value.ToString();
                    model.QuotNo = (wrkSheet.Cells[i, 6].Value == null) ? "" : (String)wrkSheet.Cells[i, 6].Value.ToString().Replace("-Q", "");
                    model.QuotDate = (wrkSheet.Cells[i, 7].Value == null) ? new DateTime() : DateTime.FromOADate((double)wrkSheet.Cells[i, 7].Value);
                    model.WorkCompletionDate = (wrkSheet.Cells[i, 8].Value == null) ? new DateTime() : DateTime.FromOADate((double)wrkSheet.Cells[i, 8].Value);
                    model.QuotAmt = (wrkSheet.Cells[i, 9].Value == null) ? 0 : decimal.Parse(wrkSheet.Cells[i, 9].Value.ToString());
                    model.InvNo = (wrkSheet.Cells[i, 10].Value == null) ? "" : (String)wrkSheet.Cells[i, 10].Value.ToString().Replace("-IN", "");
                    model.InvDate = (wrkSheet.Cells[i, 11].Value == null) ? new DateTime() : DateTime.FromOADate((double)wrkSheet.Cells[i, 11].Value);
                    model.InvAmt = (wrkSheet.Cells[i, 12].Value == null) ? 0 : decimal.Parse(wrkSheet.Cells[i, 12].Value.ToString());
                    model.DNNo = (wrkSheet.Cells[i, 13].Value == null) ? "" : (String)wrkSheet.Cells[i, 13].Value.ToString();
                    model.RowNo = i;

                    reportRecordsList.Add(model);
                }
            }

            return reportRecordsList;
        }

        public bool WriteToReport(String existingFilePath, List<ReportModel> rptModelList, int StartRowNo, int EndRowNo)
        {
            bool isSuccess = false;

            try
            {
                using (var package = new ExcelPackage(new FileInfo(existingFilePath)))
                {
                    ExcelWorksheet wrkSheet = package.Workbook.Worksheets["Report"];
                    DateTime emptyDatetime = new DateTime();

                    for (int i = StartRowNo, j = 0; i <= EndRowNo && j < rptModelList.Count; i++, j++)
                    {
                        wrkSheet.Cells[i, 1].Value = rptModelList[j].SONoOrRefNo;
                        wrkSheet.Column(2).AutoFit();
                        wrkSheet.Cells[i, 2].Value = rptModelList[j].Location;
                        wrkSheet.Column(3).AutoFit();
                        wrkSheet.Cells[i, 3].Value = rptModelList[j].Region;
                        wrkSheet.Column(4).AutoFit();
                        wrkSheet.Cells[i, 4].Value = rptModelList[j].WONo;
                        wrkSheet.Column(5).AutoFit();
                        wrkSheet.Cells[i, 5].Value = rptModelList[j].PONo;
                        wrkSheet.Column(6).AutoFit();
                        wrkSheet.Cells[i, 6].Value = rptModelList[j].QuotNo.Replace("-Q", "");
                        wrkSheet.Column(7).AutoFit();
                        wrkSheet.Cells[i, 7].Style.Numberformat.Format = "dd-mmm-yyyy";

                        if(rptModelList[j].QuotDate != emptyDatetime)
                            wrkSheet.Cells[i, 7].Value = rptModelList[j].QuotDate;

                        wrkSheet.Column(8).AutoFit();
                        wrkSheet.Cells[i, 8].Style.Numberformat.Format = "dd-mmm-yyyy";

                        if (rptModelList[j].WorkCompletionDate != emptyDatetime)
                            wrkSheet.Cells[i, 8].Value = rptModelList[j].WorkCompletionDate;

                        wrkSheet.Column(9).AutoFit();
                        wrkSheet.Cells[i, 9].Style.Numberformat.Format = "#,##0.00";

                        if (rptModelList[j].QuotAmt != 0)
                            wrkSheet.Cells[i, 9].Value = rptModelList[j].QuotAmt;

                        wrkSheet.Column(10).AutoFit();
                        wrkSheet.Cells[i, 10].Value = rptModelList[j].InvNo.Replace("-IN", "");
                        wrkSheet.Column(11).AutoFit();
                        wrkSheet.Cells[i, 11].Style.Numberformat.Format = "dd-mmm-yyyy";

                        if (rptModelList[j].InvDate != emptyDatetime)
                            wrkSheet.Cells[i, 11].Value = rptModelList[j].InvDate;

                        wrkSheet.Column(12).AutoFit();
                        wrkSheet.Cells[i, 12].Style.Numberformat.Format = "#,##0.00";

                        if (rptModelList[j].InvAmt != 0)
                            wrkSheet.Cells[i, 12].Value = rptModelList[j].InvAmt;

                        wrkSheet.Column(13).AutoFit();
                        wrkSheet.Cells[i, 13].Value = rptModelList[j].DNNo;
                    }

                    package.Save();
                }

                isSuccess = true;
            }
            catch(Exception e)
            {
                isSuccess = false;
            }

            return isSuccess;
        }

        public bool UpdateToReport(String existingFilePath, List<ReportModel> rptModelList)
        {
            bool isSuccess = false;

            try
            {
                using (var package = new ExcelPackage(new FileInfo(existingFilePath)))
                {
                    ExcelWorksheet wrkSheet = package.Workbook.Worksheets["Report"];
                    DateTime emptyDatetime = new DateTime();

                    foreach(ReportModel model in rptModelList)
                    {
                        wrkSheet.Column(4).AutoFit();
                        wrkSheet.Cells[model.RowNo, 4].Value = model.WONo;
                        wrkSheet.Column(5).AutoFit();
                        wrkSheet.Cells[model.RowNo, 5].Value = model.PONo;
                        wrkSheet.Column(6).AutoFit();
                        wrkSheet.Cells[model.RowNo, 6].Value = model.QuotNo.Replace("-Q", "");
                        wrkSheet.Column(7).AutoFit();
                        wrkSheet.Cells[model.RowNo, 7].Style.Numberformat.Format = "dd-mmm-yyyy";

                        if (model.QuotDate != emptyDatetime)
                            wrkSheet.Cells[model.RowNo, 7].Value = model.QuotDate;

                        wrkSheet.Column(8).AutoFit();
                        wrkSheet.Cells[model.RowNo, 8].Style.Numberformat.Format = "dd-mmm-yyyy";

                        if (model.WorkCompletionDate != emptyDatetime)
                            wrkSheet.Cells[model.RowNo, 8].Value = model.WorkCompletionDate;

                        wrkSheet.Column(9).AutoFit();
                        wrkSheet.Cells[model.RowNo, 9].Style.Numberformat.Format = "#,##0.00";

                        if (model.QuotAmt != 0)
                            wrkSheet.Cells[model.RowNo, 9].Value = model.QuotAmt;

                        wrkSheet.Column(10).AutoFit();
                        wrkSheet.Cells[model.RowNo, 10].Value = model.InvNo.Replace("-IN", "");
                        wrkSheet.Column(11).AutoFit();
                        wrkSheet.Cells[model.RowNo, 11].Style.Numberformat.Format = "dd-mmm-yyyy";

                        if (model.InvDate != emptyDatetime)
                            wrkSheet.Cells[model.RowNo, 11].Value = model.InvDate;

                        wrkSheet.Column(12).AutoFit();
                        wrkSheet.Cells[model.RowNo, 12].Style.Numberformat.Format = "#,##0.00";

                        if (model.InvAmt != 0)
                            wrkSheet.Cells[model.RowNo, 12].Value = model.InvAmt;

                        wrkSheet.Column(13).AutoFit();
                        wrkSheet.Cells[model.RowNo, 13].Value = model.DNNo;
                    }

                    package.Save();
                }

                isSuccess = true;
            }
            catch(Exception e)
            {
                isSuccess = false;
            }

            return isSuccess;
        }
    }
}